#include <stdio.h>
#include <sqlite3.h>
#include <string.h>

#define N 128

#define DBNAME "stu.db"

#define TBNAME "stu"

void sqliteInsert(sqlite3 *db);
void sqliteSelect(sqlite3 *db);
void sqliteQuery(sqlite3* db);
void sqliteChange(sqlite3 *db);
void sqliteDelete(sqlite3 *db);


int main(int argc, char const *argv[])
{
    sqlite3 *db=NULL;
    if(sqlite3_open(DBNAME,&db)!=SQLITE_OK){
        printf("数据库打开失败或者创建失败:%s\n",sqlite3_errmsg(db));
    }else{
        printf("数据库打开或者创建成功\n");
    }

    char sql[N]={0};
    char *errmsg=NULL;
    int errcode=0;
    sprintf(sql,"create table %s (id int primary key,name text,sex char,score int);",TBNAME);
    if((errcode=sqlite3_exec(db,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
        if(errcode==1){
            printf("打开表成功\n");
        }else{
            printf("创建表失败:%s\n",errmsg);
        }
    }else{
        printf("创建表成功\n");
    }

    int num;  

    while(1){
        printf("------------------------1.插入数据  2.查找数据 3.修改数据 4.删除数据 5.退出----------------------\n");
        printf(">>>>>>");
        if(scanf("%d",&num)!=1){
            printf("输入错误,请重新输入\n");
            while(getchar()!='\n');
            continue;
        }
       while(getchar()!='\n');

        switch(num){
            case 1:
                        sqliteInsert(db);
                        break;
            case 2:
                       //sqliteSelect(db);
                       sqliteQuery(db);
                        break;
            case 3:
                        sqliteChange(db);
                        break;
            case 4:
                        sqliteDelete(db);
                        break;
            case 5:
                        sqlite3_close(db);
                        return 0;
            default:
            printf("输入错误，请重新输入\n");
        }
    }
    return 0;
}

void sqliteInsert(sqlite3 *db){
    int id;
    char temp[32]={0};
    char name[32]={0};
    char sex;
    int score;

    char sql[N]={0};
    char *errmsg=NULL;

    fprintf(stdout,"请输入学号:\n");
    fgets(temp,32,stdin);
    temp[strlen(temp)-1]='\0';
    sscanf(temp,"%d",&id);

    fprintf(stdout,"请输入姓名:\n");
    fgets(name,32,stdin);
    name[strlen(name)-1]='\0';

    fprintf(stdout,"请输入性别:\n");
    scanf("%c",&sex);

    while (getchar() != '\n');
    fprintf(stdout,"请输入成绩:\n");
    memset(temp,0,sizeof(temp));
    fgets(temp,32,stdin);
    temp[strlen(temp)-1]='\0';
    sscanf(temp,"%d",&score);

    sprintf(sql,"insert into %s values(%d,'%s','%c',%d);",TBNAME,id,name,sex,score);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("数据插入失败\n");
    }else{
        printf("数据插入成功\n");
    }
}

int callback(void *arg,int ncolumn,char **text,char **name){
    int i=0;
    if(*(int *)arg==0){
        for(;i<ncolumn;i++){
            printf("%-11s",name[i]);
        }
        putchar(10);
        puts("---------------------------------------------------------");
        *(int *)arg=1;
    }

    for(i=0;i<ncolumn;i++){
        printf("%-11s",text[i]);
    }
    putchar(10);
    return 0;
}

void sqliteSelect(sqlite3 *db){
    char sql[N]={0};
    char* errmsg=NULL;
    int printFlag=0;
    sprintf(sql,"select * from %s;",TBNAME);
    if(sqlite3_exec(db,sql,callback,&printFlag,&errmsg)!=SQLITE_OK){
        printf("数据查询失败：%s\n",errmsg);
    }else{
        printf("数据查询成功\n");
    }
}

void sqliteQuery(sqlite3* db){
    char sql[N]={0};
    char *errmsg=NULL;
    char **ret=NULL;
    int ncolumn,nrow;
    int i,j;
    int n=0;

    sprintf(sql,"select * from %s;",TBNAME);
    if(sqlite3_get_table(db,sql,&ret,&nrow,&ncolumn,&errmsg)!=SQLITE_OK){
        printf("数据查看失败;%s",errmsg);
    }else{
        printf("数据查看成功\n");
    }

    for(int i=0;i<nrow+1;i++){
        for(int j=0;j<ncolumn;j++){
            printf("%-11s",ret[n++]);
        }
        putchar(10);
    }
}

void sqliteChange(sqlite3 *db){
    int id;
    char temp[32]={0};
    char name[32]={0};
    char sex;
    int score;

    char sql[N]={0};
    char *errmsg=NULL;

    sqliteSelect(db);

    fprintf(stdout,"请输入要修改学生的学号:\n");
    fgets(temp,32,stdin);
    temp[strlen(temp)-1]='\0';
    sscanf(temp,"%d",&id);

    fprintf(stdout,"修改姓名:\n");
    fgets(name,32,stdin);
    name[strlen(name)-1]='\0';

    fprintf(stdout,"修改性别:\n");
    scanf("%c",&sex);

    while (getchar() != '\n');
    fprintf(stdout,"修改成绩:\n");
    memset(temp,0,sizeof(temp));
    fgets(temp,32,stdin);
    temp[strlen(temp)-1]='\0';
    sscanf(temp,"%d",&score);

    sprintf(sql,"update %s set name='%s' where id=%d;",TBNAME,name,id);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("name修改失败:%s\n",errmsg);
    }else{
        printf("name修改成功\n");
    }

    memset(sql,0,sizeof(sql));
    sprintf(sql,"update %s set sex='%c' where id=%d;",TBNAME,sex,id);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("sex修改失败:%s\n",errmsg);
    }else{
        printf("sex修改成功\n");
    }

    memset(sql,0,sizeof(sql));
    sprintf(sql,"update %s set score=%d where id=%d;",TBNAME,score,id);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("score修改失败:%s\n",errmsg);
    }else{
        printf("score修改成功\n");
    }
}

void sqliteDelete(sqlite3 *db){
    int id;
    char sql[N]={0};
    char temp[32]={0};
    char* errmsg=NULL;

    sqliteSelect(db);

    printf("请输入要删除数据的id:");
    fgets(temp,32,stdin);
    temp[strlen(temp)-1]='\0';
    sscanf(temp,"%d",&id);

    sprintf(sql,"delete from %s where id=%d;",TBNAME,id);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("删除数据失败:%s\n",errmsg);
    }else{
        printf("删除数据成功\n");
    }
}
